{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "\n",
    "import re\n",
    "import os\n",
    "import gc\n",
    "import sys\n",
    "import time\n",
    "import json\n",
    "import joblib\n",
    "import traceback\n",
    "from tqdm import tqdm\n",
    "from urllib.parse import quote_plus\n",
    "import pymongo\n",
    "from pymongo import UpdateOne\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import polars as pl\n",
    "import polars.selectors as cs\n",
    "from loguru import logger\n",
    "from datetime import timedelta, datetime, date\n",
    "from dateutil.relativedelta import relativedelta\n",
    "\n",
    "import config\n",
    "from scorecardpipeline import *\n",
    "from dbutils.mysql_connect_pool import MysqlConnectPool\n",
    "\n",
    "\n",
    "starrocks_uri = \"mysql://user:password@host:9030\"\n",
    "mongodb_url = \"mongodb://%s:%s@%s\" % (quote_plus(\"user\"), quote_plus(\"password\"), \"host:3717\")\n",
    "\n",
    "starrocks = MysqlConnectPool(**config.starrocks_connect_options, **config.pool_options) # starrocks 连接池\n",
    "router_data = pymongo.MongoClient(query_mongodb_url + \"/router_data\")[\"router_data\"][\"router_data\"] #三方数据结果\n",
    "\n",
    "init_setting()\n",
    "pd.set_option('display.max_columns', 50)\n",
    "\n",
    "\n",
    "def deal_columns(data_name):\n",
    "    return eval(f\"list(set({data_name}.columns.drop('routeResponse').tolist() + list({data_name}_map.keys())))\")\n",
    "\n",
    "\n",
    "def flatten_dict(d, parent_key='', sep='_'):\n",
    "    \"\"\"\n",
    "    将嵌套的字典转换为单层的字典\n",
    "    \"\"\"\n",
    "    if pd.isnull(d):\n",
    "        return {}\n",
    "    else:\n",
    "        items = []\n",
    "        for k, v in d.items():\n",
    "            new_key = f\"{parent_key}{sep}{k}\" if parent_key else k\n",
    "            if isinstance(v, dict):\n",
    "                items.extend(flatten_dict(v, new_key, sep=sep).items())\n",
    "            else:\n",
    "                items.append((new_key, v))\n",
    "        return dict(items)\n",
    "\n",
    "\n",
    "def query_mongodb(query, projection=None, database=\"router_data\", collect=\"router_data\", *args):\n",
    "    current_query = pymongo.MongoClient(mongodb_url + f\"/{database}\")[database][collect]\n",
    "\n",
    "    return pd.DataFrame(current_query.find(query, projection=projection))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 获取订单数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = starrocks.query(\"\"\"\n",
    "    SELECT n.id 授信编号\n",
    "        , md5(l.cust_name) 姓名\n",
    "        , md5(l.cust_mobile) 手机号\n",
    "        , md5(l.cust_cert_no) 身份证\n",
    "        , left(l.cust_cert_no, 2) 户籍省份\n",
    "        , cast(cast(substr(l.cust_cert_no, 17, 1) as int) % 2 as STRING) 性别\n",
    "        , year(u.created_time) - cast(substr(l.cust_cert_no, 7, 4) as int) 年龄\n",
    "        , l.loan_amt 放款金额\n",
    "        , l.periods 放款期数\n",
    "        , u.created_time 回溯时间\n",
    "        , l.loan_time 放款时间\n",
    "        , r.DPD, r.MOB1, r.MOB2, r.MOB3, r.CURRENT_DPD\n",
    "    FROM qy_ods.s06_zj_loan l\n",
    "    LEFT JOIN qy_ods.s01_360_360_loan n ON l.id = n.loan_no\n",
    "    INNER JOIN (\n",
    "        SELECT loan_id\n",
    "                , max(CASE WHEN repaid_date < repay_date OR repaid_date IS NOT NULL THEN 0 ELSE datediff(current_date(), repay_date) END) CURRENT_DPD\n",
    "                , max(CASE WHEN repaid_date < repay_date THEN 0 ELSE datediff(ifnull(repaid_date, current_date()), repay_date) END) DPD\n",
    "                , max(CASE WHEN repaid_date < repay_date OR period > 1 THEN 0 ELSE datediff(ifnull(repaid_date, current_date()), repay_date) END) MOB1\n",
    "                , max(CASE WHEN repaid_date < repay_date OR period > 2 THEN 0 ELSE datediff(ifnull(repaid_date, current_date()), repay_date) END) MOB2\n",
    "                , max(CASE WHEN repaid_date < repay_date OR period > 3 THEN 0 ELSE datediff(ifnull(repaid_date, current_date()), repay_date) END) MOB3\n",
    "        FROM (\n",
    "            SELECT p.*\n",
    "                    , IF(p.period >= c.period, c.act_repay_time, r.act_repay_time) repaid_date\n",
    "                    , IF(p.period = c.period, c.act_principal_amt, r.act_principal_amt) act_principal_amt\n",
    "            FROM qy_ods.s06_zj_loan_replan p\n",
    "            LEFT JOIN (\n",
    "                select loan_id, period, sum(act_principal_amt) act_principal_amt, date(max(act_repay_time)) act_repay_time, group_concat(repay_mode) repay_mode\n",
    "                from qy_ods.s06_zj_customer_loan_replan\n",
    "                where repay_purpose = 'CURRENT'\n",
    "                group by 1, 2\n",
    "            ) r on p.loan_id = r.loan_id and p.period = r.period\n",
    "            LEFT JOIN (\n",
    "                select loan_id, ifnull(period, 1) period, sum(act_principal_amt) act_principal_amt, date(max(act_repay_time)) act_repay_time, group_concat(repay_mode) repay_mode\n",
    "                from qy_ods.s06_zj_customer_loan_replan\n",
    "                where repay_purpose = 'CLEAR' AND act_principal_amt > 0\n",
    "                group by 1, 2\n",
    "            ) c ON p.loan_id = c.loan_id\n",
    "        ) t\n",
    "        WHERE repay_date < current_date()\n",
    "        GROUP BY 1\n",
    "    ) r ON l.id = r.loan_id\n",
    "    where l.loan_status = 'SUCCESS'\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 查询三方数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def iter_order_list(order, batch_size=50000):\n",
    "    for i in tqdm(range(0, len(order), batch_size)):\n",
    "        yield order[\"授信编号\"].tolist()[i:i+batch_size]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "lasted_order = pd.DataFrame()\n",
    "for order_list in iter_order_list(data, batch_size=50000):\n",
    "    temp = starrocks.query(f\"\"\"\n",
    "        SELECT credit_id, create_time\n",
    "            -- , REPLACE(JSON_QUERY(parse_json(rc.credit_content), '$.zh_chnn_code'), '\"', '') channel_code\n",
    "            -- , JSON_QUERY(parse_json(rc.credit_content), '$.event_code') event_code\n",
    "            -- , REPLACE(JSON_QUERY(parse_json(rc.credit_content), IF(JSON_QUERY(parse_json(rc.credit_content), '$.event_code') = 50, '$.notifyContentPlain.ruleDetail.policyName', '$.rule_detail.policyName')), '\"', '') policyName\n",
    "            -- , REPLACE(JSON_QUERY(parse_json(rc.credit_content), IF(JSON_QUERY(parse_json(rc.credit_content), '$.event_code') = 50, '$.notifyContentPlain.ruleDetail.policyNameE', '$.rule_detail.policyNameE')), '\"', '') policyNameE\n",
    "            , REPLACE(JSON_QUERY(parse_json(rc.credit_content), '$.label_1'), '\"', '') label_1\n",
    "            , REPLACE(JSON_QUERY(parse_json(rc.credit_content), '$.label_2'), '\"', '') label_2\n",
    "            , REPLACE(JSON_QUERY(parse_json(rc.credit_content), '$.status_1'), '\"', '') status_1\n",
    "            , REPLACE(JSON_QUERY(parse_json(rc.credit_content), '$.status_2'), '\"', '') status_2\n",
    "            , REPLACE(JSON_QUERY(parse_json(rc.credit_content), '$.level_1'), '\"', '') level_1\n",
    "            , REPLACE(JSON_QUERY(parse_json(rc.credit_content), '$.level_2'), '\"', '') level_2\n",
    "            , REPLACE(JSON_QUERY(parse_json(rc.credit_content), '$.ovd_level_1'), '\"', '') ovd_level_1\n",
    "            , REPLACE(JSON_QUERY(parse_json(rc.credit_content), '$.ovd_level_2'), '\"', '') ovd_level_2\n",
    "            , REPLACE(JSON_QUERY(parse_json(rc.credit_content), '$.cust_type'), '\"', '') cust_type\n",
    "        FROM qy_ods.s06_fk_riskengine_credit rc\n",
    "        WHERE credit_id in ({str(order_list)[1:-1]})\n",
    "    \"\"\")\n",
    "\n",
    "    lasted_order = pd.concat([lasted_order, temp])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "temp = pd.read_excel(\"feature_maps/百融/百融云创零售数据产品介绍2023.08.22.xlsx\", sheet_name=\"ApplyLoanStrV2.0\", skiprows=14, usecols=[2, 5]).dropna().drop_duplicates(\"中文名称\")\n",
    "br_loan_intention_v2_map = dict(zip(temp[\"参数名\"], temp[\"中文名称\"].apply(lambda x: x.replace(\"，\", \"\"))))\n",
    "\n",
    "br_loan_intention_v2 = pd.DataFrame()\n",
    "for order_list in iter_order_list(data, batch_size=10000):\n",
    "    temp = starrocks.query(f\"\"\"\n",
    "        SELECT creditId, createTime, routeResponse\n",
    "        FROM qy_ods.s06_fk_br_loan_intention_v2\n",
    "        WHERE creditId in  ({str(data['授信编号'].tolist())[1:-1]})\n",
    "    \"\"\")\n",
    "    br_loan_intention_v2 = pd.concat([br_loan_intention_v2, temp])\n",
    "\n",
    "if len(br_loan_intention_v2) > 0:\n",
    "    br_loan_intention_v2 = br_loan_intention_v2.sort_values(\"createTime\").drop_duplicates(subset=[\"creditId\"], keep=\"last\").reset_index(drop=True)\n",
    "    br_loan_intention_v2 = br_loan_intention_v2.join(br_loan_intention_v2[\"routeResponse\"].apply(lambda x: json.loads(x if x and x != '\"\"' else \"{}\").get(\"content\", {})).apply(pd.Series))\n",
    "    br_loan_intention_v2 = br_loan_intention_v2.reindex(columns=deal_columns(\"br_loan_intention_v2\"))\n",
    "    br_loan_intention_v2[list(br_loan_intention_v2_map.keys())] = br_loan_intention_v2[br_loan_intention_v2_map.keys()].astype(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bh_fraud_dongjian = pd.DataFrame()\n",
    "for order_list in iter_order_list(data, batch_size=10000):\n",
    "    temp = starrocks.query(f\"\"\"\n",
    "        SELECT creditId, createTime, routeResponse\n",
    "        FROM qy_ods.s06_fk_bh_fraud_dongjian\n",
    "        WHERE creditId in ({str(order_list)[1:-1]})\n",
    "    \"\"\")\n",
    "    bh_fraud_dongjian = pd.concat([bh_fraud_dongjian, temp])\n",
    "\n",
    "if len(bh_fraud_dongjian) > 0:\n",
    "    bh_fraud_dongjian = bh_fraud_dongjian.sort_values(\"createTime\").drop_duplicates(subset=[\"creditId\"], keep=\"last\").reset_index(drop=True)\n",
    "    bh_fraud_dongjian[\"BH_DJ_SCORE\"] = bh_fraud_dongjian[\"routeResponse\"].apply(lambda x: json.loads(x).get(\"score\"))\n",
    "    bh_fraud_dongjian = bh_fraud_dongjian[[\"creditId\", \"BH_DJ_SCORE\"]].join(bh_fraud_dongjian[\"routeResponse\"].apply(lambda x: json.loads(x).get(\"contents\")).apply(pd.Series))\n",
    "    bh_fraud_dongjian = bh_fraud_dongjian.replace(\"\", np.nan)\n",
    "    number_cols = bh_fraud_dongjian.columns.drop(['creditId', 'BH_G011', 'BH_G012', 'BH_G013', 'BH_G014', 'BH_G015']).tolist()\n",
    "    bh_fraud_dongjian[number_cols] = bh_fraud_dongjian[number_cols].astype(float)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 合并数据集"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_data = data.merge(\n",
    "    br_loan_intention_v2[[\"creditId\"] + [c for c in br_loan_intention_v2.columns if 'als' in c]].rename(columns={\"creditId\": \"授信编号\"}), on=\"授信编号\", how=\"inner\"\n",
    ").merge(\n",
    "    bh_fraud_dongjian.drop(columns=[\"BH_G011\", \"BH_G012\", \"BH_G013\", \"BH_G014\", \"BH_G015\"]).rename(columns={\"creditId\": \"授信编号\"}), on=\"授信编号\", how=\"inner\"\n",
    ").merge(\n",
    "    lasted_order.rename(columns={\"credit_id\": \"授信编号\"}), on=\"授信编号\", how=\"inner\"\n",
    ").drop(columns=[\"身份证\", \"手机号\", \"姓名\", \"申请时间\", \"回溯时间\"])\n",
    "\n",
    "all_data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 拆分数据集"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mob, dpd, oot_days = 1, 15, 15\n",
    "oot_date = (date.today() - relativedelta(months=mob, days=dpd + oot_days)).strftime(\"%Y-%m-%d\")\n",
    "split_date = (date.today() - relativedelta(months=mob, days=dpd)).strftime(\"%Y-%m-%d\")\n",
    "train = all_data.query(\"(MOB1 <= 3 | MOB1 > 15) & 放款时间 < @oot_date\").assign(target=lambda x: (x[\"MOB1\"] > 15).astype(int))\n",
    "oot = all_data.query(\"放款时间 >= @oot_date & 放款时间 < @split_date\").assign(target=lambda x: (x[\"MOB1\"] > 7).astype(int))\n",
    "print(oot_date, split_date, train.shape, oot.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 特征筛选"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 当数据集比较大时，采样部分数据用于特征筛选\n",
    "feature_select_samples = pd.concat([\n",
    "    all_data.query(\"MOB1 > 15 & 放款时间 < @oot_date\").assign(target=lambda x: (x[\"MOB1\"] > 15).astype(int)),\n",
    "    all_data.query(\"MOB1 == 0 & 放款时间 < @oot_date\").assign(target=lambda x: (x[\"MOB1\"] > 15).astype(int)).sample(n=50000),\n",
    "]).sample(frac=1.0).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "selection = FeatureSelection(empty=0.95, iv=0.02, corr=0.6, identical=0.95, engine=\"toad\")\n",
    "feature_select_samples = selection.fit_transform(feature_select_samples.drop(columns=[\"授信编号\", \"渠道编码\", \"资产类型\", \"放款金额\", \"放款期数\", \"放款时间\", \"DPD\", \"MOB1\", \"MOB2\", \"MOB3\", \"CURRENT_DPD\", \"Extended\", \"户籍省份\"]))\n",
    "print(feature_select_samples.select_dtypes(object).columns, feature_select_samples.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cat_rules = {}\n",
    "for c in feature_select_samples.select_dtypes(object).columns:\n",
    "    cat_rules[c] = [[c] for c in feature_select_samples[c].unique()] + [[np.nan]]\n",
    "\n",
    "combiner = Combiner(method=\"mdlp\", max_n_bins=5, min_prebin_size=0.01, min_bin_size=0.025, gamma=0.01, target=\"target\", adj_rules=cat_rules, n_jobs=-1)\n",
    "combiner.fit(feature_select_samples)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "psi_test = all_data.query(\"(MOB1 <= 3 | MOB1 > 15) & 放款时间 < @oot_date & 放款时间 >= '2024-11-15'\")\n",
    "psi_base = all_data.query(\"(MOB1 <= 3 | MOB1 > 15) & 放款时间 < '2024-11-15'\")\n",
    "\n",
    "psi_info = {}\n",
    "for c in selection.select_columns[:-1]:\n",
    "    psi_info[c] = PSI(combiner.transform(psi_test[c]), combiner.transform(psi_base[c]))\n",
    "\n",
    "psi_info = pd.Series(psi_info)\n",
    "psi_select_columns = psi_info[psi_info < 0.01].index.tolist() + [\"target\"]\n",
    "\n",
    "feature_select_samples = feature_select_samples[psi_select_columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "woe = WOETransformer(target=\"target\")\n",
    "woe.fit(combiner.transform(feature_select_samples))\n",
    "\n",
    "feature_select_samples_woe = woe.transform(combiner.transform(feature_select_samples))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "selection = FeatureSelection(empty=0.95, iv=0.03, corr=0.6, identical=0.95, engine=\"toad\")\n",
    "selection.fit(feature_select_samples_woe)\n",
    "\n",
    "feature_select_samples_woe = selection.transform(feature_select_samples_woe)\n",
    "feature_select_samples_woe.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import target_permutation_importances as tpi\n",
    "from xgboost import XGBClassifier\n",
    "\n",
    "wrapped_model = tpi.TargetPermutationImportancesWrapper(\n",
    "    model_cls=XGBClassifier,\n",
    "    model_cls_params={\n",
    "        'gamma': 4.594259199851212,\n",
    "        'scale_pos_weight': 31.979731342546017,\n",
    "        'learning_rate': 0.004401689684274711,\n",
    "        'n_estimators': 144,\n",
    "        'reg_alpha': 0.8735734418747836,\n",
    "        'reg_lambda': 78.80453596258465,\n",
    "        'max_depth': 5,\n",
    "        'subsample': 0.479347123407695,\n",
    "        'colsample_bytree': 0.8195699784968611,\n",
    "        'min_child_weight': 32,\n",
    "        'objective': 'binary:logistic',\n",
    "        'eval_metric': 'logloss',\n",
    "        'verbosity': 0,\n",
    "        'importance_type': 'gain',\n",
    "        'booster': 'gbtree',\n",
    "        # \"device\": \"cuda\",\n",
    "    },\n",
    "    num_actual_runs=10,\n",
    "    num_random_runs=20,\n",
    "    shuffle_feature_order=True,\n",
    "    permutation_importance_calculator=tpi.compute_permutation_importance_by_subtraction,\n",
    ")\n",
    "\n",
    "wrapped_model.fit(feature_select_samples_woe.drop(\"target\", axis=1), feature_select_samples_woe[\"target\"])\n",
    "select_columns = list(set(wrapped_model.feature_importances_df.sort_values(\"importance\", ascending=False).query(\"importance >= 0.002\")[\"feature\"].tolist())) + [\"target\"]\n",
    "wrapped_model.feature_importances_df.sort_values(\"importance\", ascending=False).query(\"importance >= 0.002\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 最终训练数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_select = train[select_columns]\n",
    "oot_select = oot[select_columns]\n",
    "\n",
    "if len(train_select.select_dtypes(object).columns) > 0:\n",
    "    woe_encoder = WOETransformer(target=\"target\")\n",
    "    woe_encoder.fit(combiner.transform(train_select[train_select.select_dtypes(object).columns.tolist() + [\"target\"]]))\n",
    "\n",
    "    for c in tqdm(train_select.select_dtypes(object).columns):\n",
    "        train_select[c] = woe_encoder.transform(combiner.transform(train_select[c]))\n",
    "        oot_select[c] = woe_encoder.transform(combiner.transform(oot_select[c]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CATBOOST 模型训练"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.metrics import *\n",
    "from sklearn.model_selection import train_test_split\n",
    "\n",
    "import optuna\n",
    "import optunahub\n",
    "from catboost import CatBoostClassifier, Pool\n",
    "\n",
    "\n",
    "def objective(trial, train, test_size=0.3):\n",
    "    target = \"target\"\n",
    "    param = dict(\n",
    "        depth=trial.suggest_int(\"depth\", 2, 5)\n",
    "         , min_data_in_leaf=trial.suggest_int('min_data_in_leaf', 128, 512, 64)\n",
    "         , num_boost_round=trial.suggest_int('num_boost_round', 64, 512)\n",
    "         , early_stopping_rounds=5\n",
    "         , l2_leaf_reg=trial.suggest_float('l2_leaf_reg', 10, 64)\n",
    "         , learning_rate=trial.suggest_float('learning_rate', 1e-4, 0.01)\n",
    "         , eval_metric=f\"Focal:focal_alpha={trial.suggest_float('focal_alpha', 0.1, 0.5)};focal_gamma={trial.suggest_float('focal_gamma', 1.0, 10.0)}\"\n",
    "         # , eval_metric=\"Logloss\"\n",
    "         # , loss_function=\"CrossEntropy\"\n",
    "         , loss_function=\"Logloss\"\n",
    "         , model_size_reg=trial.suggest_float('model_size_reg', 10, 64)\n",
    "         , subsample=trial.suggest_float('subsample', 0.6, 1.0)\n",
    "         , bagging_temperature=trial.suggest_float('bagging_temperature', 0.6, 1.0)\n",
    "         , use_best_model=True\n",
    "         , rsm=trial.suggest_float('rsm', 0.6, 1.0)\n",
    "         , scale_pos_weight=trial.suggest_float('scale_pos_weight', 0.5, 32.0)\n",
    "         # , task_type=\"GPU\"\n",
    "         # , devices=\"0\"\n",
    "    )\n",
    "    \n",
    "    _train, _test = train_test_split(train, test_size=test_size, stratify=train[target])\n",
    "    clf = CatBoostClassifier(silent=True, **param)\n",
    "\n",
    "    clf.fit(\n",
    "        _train.drop(target, axis=1),\n",
    "        _train[target],\n",
    "        eval_set=(_test.drop(target, axis=1), _test[target]),\n",
    "        # cat_features=cat_features,\n",
    "    )\n",
    "    \n",
    "    _y_train_pred = clf.predict_proba(_train.drop(target, axis=1))[:, 1]\n",
    "    _y_test_pred = clf.predict_proba(_test.drop(target, axis=1))[:, 1]\n",
    "\n",
    "    # threshold = np.percentile(_y_test_pred, 0.97)\n",
    "    # return f1_score(_train[target], _y_train_pred > threshold), f1_score(_test[target], _y_test_pred > threshold)\n",
    "\n",
    "    # score = 0.3 * KS(_y_train_pred, _train[target]) + 0.7 * KS(_y_test_pred, _test[target])\n",
    "\n",
    "    return KS(_y_train_pred, _train[target]), KS(_y_test_pred, _test[target])\n",
    "\n",
    "# 启动optuna监控页面: optuna-dashboard sqlite:///catboost.db --host=0.0.0.0\n",
    "study = optuna.create_study(directions=[\"maximize\", \"maximize\"] #\"minimize\"\n",
    "    # , study_name=\"catboost\"\n",
    "    # , sampler=optuna.samplers.TPESampler()\n",
    "    , sampler=optunahub.load_module(\"samplers/auto_sampler\").AutoSampler()\n",
    "    , storage=\"sqlite:///catboost.db\"\n",
    ")\n",
    "\n",
    "study.optimize(lambda trial: objective(trial, train_select), show_progress_bar=True, n_trials=64)\n",
    "# optuna.visualization.plot_pareto_front(study, target_names=[\"TRAIN KS\", \"TEST KS\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "best_params = study.trials[63].params\n",
    "param = dict(\n",
    "        depth=best_params.get(\"depth\")\n",
    "         , min_data_in_leaf=best_params.get('min_data_in_leaf')\n",
    "         , num_boost_round=best_params.get('num_boost_round')\n",
    "         , early_stopping_rounds=5\n",
    "         , l2_leaf_reg=best_params.get('l2_leaf_reg')\n",
    "         , learning_rate=best_params.get('learning_rate')\n",
    "         , eval_metric=f\"Focal:focal_alpha={best_params.get('focal_alpha')};focal_gamma={best_params.get('focal_gamma')}\"\n",
    "         # , eval_metric=\"Logloss\"\n",
    "         # , loss_function=\"CrossEntropy\"\n",
    "         , loss_function=\"Logloss\"\n",
    "         , model_size_reg=best_params.get('model_size_reg')\n",
    "         , subsample=best_params.get('subsample')\n",
    "         , bagging_temperature=best_params.get('bagging_temperature')\n",
    "         , use_best_model=True\n",
    "         , rsm=best_params.get('rsm')\n",
    "         , silent=True\n",
    "         , scale_pos_weight=best_params.get('scale_pos_weight')\n",
    "    )\n",
    "\n",
    "catboost_model = CatBoostClassifier(**param, random_seed=8888)\n",
    "print(param)\n",
    "\n",
    "cat_features = train_select.select_dtypes(object).columns.tolist()\n",
    "\n",
    "catboost_model.fit(\n",
    "    train_select.drop([\"target\"], axis=1),\n",
    "    train_select[\"target\"],\n",
    "    eval_set=[\n",
    "        (train_select.drop([\"target\"], axis=1), train_select[\"target\"]),\n",
    "    ],\n",
    "    # cat_features=cat_features,\n",
    ")\n",
    "\n",
    "print(KS(catboost_model.predict_proba(train_select.drop([\"target\"], axis=1))[:, 1], train_select[\"target\"]), KS(catboost_model.predict_proba(oot_select.drop([\"target\"], axis=1))[:, 1], oot_select[\"target\"]))\n",
    "\n",
    "temp = oot.assign(\n",
    "    score=lambda x: catboost_model.predict_proba(\n",
    "        x[catboost_model.feature_names_].assign(\n",
    "            客群状态1=lambda x: woe_encoder.transform(combiner.transform(x.客群状态1)),\n",
    "        )\n",
    "    )[:, 1]\n",
    ")\n",
    "\n",
    "table_oot = feature_bin_stats(\n",
    "    temp\n",
    "    , \"score\", method=\"mdlp\", rules=temp[\"score\"].quantile([0.1, 0.3, 0.5, 0.7, 0.9, 0.95, 0.97, 0.98, 0.99]).unique().tolist()\n",
    "    , overdue=[f\"MOB{mob}\"], dpd=[dpd, 3, 0], max_n_bins=10, min_bin_size=0.001, return_cols=[\"坏样本数\", \"坏样本率\", \"LIFT值\", \"累积LIFT值\", \"分档KS值\"]\n",
    ")\n",
    "\n",
    "ks_plot(temp[\"score\"], temp[\"target\"], figsize=(10, 5), title=\"OOT评分\");\n",
    "bin_plot(table_oot, desc=f\"OOT评分\");\n",
    "display(table_oot)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### XGBOOST 模型训练"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.metrics import *\n",
    "from sklearn.model_selection import train_test_split\n",
    "\n",
    "import optuna\n",
    "import optunahub\n",
    "import xgboost as xgb\n",
    "from xgboost import XGBClassifier\n",
    "\n",
    "def objective(trial, train, test_size=0.2):\n",
    "    # 设置大范围搜索参数\n",
    "    # param = {\n",
    "    #     \"objective\": \"binary:logistic\", # for binary classification\n",
    "    #     \"eval_metric\": \"auc\", # for log loss\n",
    "    #     \"verbosity\": 0,\n",
    "    #     \"booster\": \"gbtree\",\n",
    "    #     \"gamma\": trial.suggest_float(\"gamma\", 0.0, 32.0),\n",
    "    #     \"scale_pos_weight\": trial.suggest_float(\"scale_pos_weight\", 16.0, 32.0),\n",
    "    #     \"learning_rate\": trial.suggest_float(\"learning_rate\", 0.0001, 0.01),\n",
    "    #     \"n_estimators\": trial.suggest_int(\"n_estimators\", 32, 256, 16),\n",
    "    #     \"reg_alpha\": trial.suggest_float(\"reg_alpha\", 0.0, 1.0),\n",
    "    #     \"reg_lambda\": trial.suggest_float(\"reg_lambda\", 32.0, 128.0),\n",
    "    #     \"max_depth\": trial.suggest_int(\"max_depth\", 3, 5),\n",
    "    #     \"subsample\": trial.suggest_float(\"subsample\", 0.35, 0.85),\n",
    "    #     \"colsample_bytree\": trial.suggest_float(\"colsample_bytree\", 0.4, 0.9),\n",
    "    #     \"min_child_weight\": trial.suggest_int(\"min_child_weight\", 8, 256, 4),\n",
    "    # }\n",
    "\n",
    "    # 基于最优参数缩小范围进一步搜索\n",
    "    param = {\n",
    "        \"objective\": \"binary:logistic\",\n",
    "        \"eval_metric\": \"auc\",\n",
    "        \"verbosity\": 0,\n",
    "        \"booster\": \"gbtree\",\n",
    "        \"gamma\": trial.suggest_float(\"gamma\", 4, 32.0),\n",
    "        \"scale_pos_weight\": trial.suggest_float(\"scale_pos_weight\", 22, 32.0),\n",
    "        \"learning_rate\": trial.suggest_float(\"learning_rate\", 0.004, 0.01),\n",
    "        \"n_estimators\": trial.suggest_int(\"n_estimators\", 90, 150),\n",
    "        \"reg_alpha\": trial.suggest_float(\"reg_alpha\", 0.3, 0.9),\n",
    "        \"reg_lambda\": trial.suggest_float(\"reg_lambda\", 45.0, 80.0),\n",
    "        \"max_depth\": trial.suggest_int(\"max_depth\", 4, 6),\n",
    "        \"subsample\": trial.suggest_float(\"subsample\", 0.4, 0.8),\n",
    "        \"colsample_bytree\": trial.suggest_float(\"colsample_bytree\", 0.5, 0.85),\n",
    "        \"min_child_weight\": trial.suggest_int(\"min_child_weight\", 32, 128),\n",
    "        \"device\": \"cuda\",\n",
    "    }\n",
    "    \n",
    "    X_train, X_test, y_train, y_test = train_test_split(train.drop(columns=\"target\"), train[\"target\"], test_size=test_size, stratify=train[\"target\"])\n",
    "\n",
    "    dtrain = xgb.DMatrix(X_train, label=y_train)\n",
    "    dtest = xgb.DMatrix(X_test, label=y_test)\n",
    "    \n",
    "    # pruning_callback = optuna.integration.XGBoostPruningCallback(trial, \"test-auc\")\n",
    "    # xgb_classifier = xgb.cv(param, dtrain, callbacks=[pruning_callback])\n",
    "    # xgb_classifier = xgb.train(param, dtrain, evals=[(dtrain, \"train\"), (dtest, \"test\")], callbacks=[pruning_callback])\n",
    "    xgb_classifier = xgb.train(param, dtrain, evals=[(dtrain, \"train\"), (dtest, \"test\")], early_stopping_rounds=10)\n",
    "\n",
    "    # pruning_callback = optuna.integration.XGBoostPruningCallback(trial, 'validation_0-auc')\n",
    "    # xgb_classifier = XGBClassifier(**param) #, callbacks=[pruning_callback])\n",
    "    # xgb_classifier.fit(X_train, y_train)\n",
    "\n",
    "    y_train_proba = xgb_classifier.predict(dtrain)\n",
    "    y_test_proba = xgb_classifier.predict(dtest)\n",
    "    \n",
    "    # y_train_proba = xgb_classifier.predict_proba(X_train)[:, 1]\n",
    "    # y_test_proba = xgb_classifier.predict_proba(X_test)[:, 1]\n",
    "\n",
    "    # return 0.3 * KS(y_train_proba, y_train) + 0.7 * KS(y_test_proba, y_test)\n",
    "\n",
    "    temp = pd.DataFrame({\"score\": y_test_proba, \"target\": y_test})\n",
    "    table = feature_bin_stats(temp, \"score\", rules=temp[\"score\"].quantile([0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9, 0.95, 0.97, 0.98, 0.99]).tolist(), target=\"target\", return_cols=[\"坏样本率\", \"LIFT值\", \"累积LIFT值\"])\n",
    "    coeffs = np.polyfit([0.0, 0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9, 0.95, 0.97, 0.98, 0.99], table[\"LIFT值\"].values.tolist(), 2)\n",
    "\n",
    "    # trial.set_user_attr(\"auc-error\", abs(AUC(y_train_proba, y_train) - AUC(y_test_proba, y_test)))\n",
    "    # trial.set_user_attr(\"ks train\", KS(y_train_proba, y_train))\n",
    "    # trial.set_user_attr(\"ks test\", KS(y_test_proba, y_test))\n",
    "\n",
    "    return abs(coeffs[0]) / 3.0, test_size * KS(y_train_proba, y_train) + (1 - test_size) * KS(y_test_proba, y_test)\n",
    "\n",
    "    # # threshold = np.percentile(y_test_proba, 0.97)\n",
    "    # # return 0.3 * KS(y_train_proba, y_train) + 0.7 * KS(y_test_proba, y_test), 0.3 * f1_score(y_train, y_train_proba > threshold) + 0.7 * f1_score(y_test, y_test_proba > threshold)\n",
    "    # # return f1_score(y_train, y_train_proba > threshold), f1_score(y_test, y_test_proba > threshold), KS(y_test_proba, y_test)\n",
    "\n",
    "\n",
    "# 启动optuna监控页面: optuna-dashboard sqlite:///xgboost.db --host=0.0.0.0\n",
    "study = optuna.create_study(\n",
    "    directions=[\"maximize\", \"maximize\"] #\"minimize\"\n",
    "    # , study_name=\"xgboost\"\n",
    "    # , sampler=optuna.samplers.TPESampler()\n",
    "    , sampler=optunahub.load_module(\"samplers/auto_sampler\").AutoSampler()\n",
    "    # , pruner=optuna.pruners.MedianPruner(n_warmup_steps=10)\n",
    "    , storage=\"sqlite:///xgboost.db\"\n",
    ")\n",
    "\n",
    "# 指定某些超参数点搜索\n",
    "study.enqueue_trial(\n",
    "    {\n",
    "        'gamma': 4.594259199851212,\n",
    "        'scale_pos_weight': 31.979731342546017,\n",
    "        'learning_rate': 0.004401689684274711,\n",
    "        'n_estimators': 144,\n",
    "        'reg_alpha': 0.8735734418747836,\n",
    "        'reg_lambda': 78.80453596258465,\n",
    "        'max_depth': 5,\n",
    "        'subsample': 0.479347123407695,\n",
    "        'colsample_bytree': 0.8195699784968611,\n",
    "        'min_child_weight': 32,\n",
    "        'objective': 'binary:logistic',\n",
    "        'eval_metric': 'auc',\n",
    "        'verbosity': 0,\n",
    "        'booster': 'gbtree',\n",
    "        \"device\": \"cuda\",\n",
    "    }\n",
    ")\n",
    "\n",
    "study.enqueue_trial(\n",
    "    {\n",
    "        'objective': 'binary:logistic',\n",
    "        'eval_metric': 'auc',\n",
    "        'verbosity': 0,\n",
    "        'booster': 'gbtree',\n",
    "        'gamma': 20.618639646365324,\n",
    "        'scale_pos_weight': 28.46817520972572,\n",
    "        'learning_rate': 0.007842004642961626,\n",
    "        'n_estimators': 115,\n",
    "        'reg_alpha': 0.7437452635785256,\n",
    "        'reg_lambda': 50.96599548821085,\n",
    "        'max_depth': 6,\n",
    "        'subsample': 0.7044268776337913,\n",
    "        'colsample_bytree': 0.7708775556331752,\n",
    "        'min_child_weight': 72,\n",
    "        \"device\": \"cuda\",\n",
    "    }\n",
    ")\n",
    "\n",
    "study.optimize(lambda trial: objective(trial, train_select, test_size=0.1), n_trials=64)\n",
    "# optuna.visualization.plot_pareto_front(study, target_names=[\"SLOPE\", \"KS\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "trials_point = [5]\n",
    "for best_params in [study.trials[i] for i in trials_point]:\n",
    "    param = {\n",
    "        \"objective\": \"binary:logistic\",\n",
    "        \"eval_metric\": \"auc\",\n",
    "        \"verbosity\": 0,\n",
    "        \"booster\": \"gbtree\",\n",
    "        \"gamma\": best_params.params.get(\"gamma\"),\n",
    "        \"scale_pos_weight\": best_params.params.get(\"scale_pos_weight\"),\n",
    "        \"learning_rate\": best_params.params.get(\"learning_rate\"),\n",
    "        \"n_estimators\": best_params.params.get(\"n_estimators\"),\n",
    "        \"reg_alpha\": best_params.params.get(\"reg_alpha\"),\n",
    "        \"reg_lambda\": best_params.params.get(\"reg_lambda\"),\n",
    "        \"max_depth\": best_params.params.get(\"max_depth\"),\n",
    "        \"subsample\": best_params.params.get(\"subsample\"),\n",
    "        \"colsample_bytree\": best_params.params.get(\"colsample_bytree\"),\n",
    "        \"min_child_weight\": best_params.params.get(\"min_child_weight\"),\n",
    "    }\n",
    "\n",
    "    param.update({\"objective\": \"binary:logistic\", \"eval_metric\": \"logloss\", \"verbosity\": 0, \"booster\": \"gbtree\", \"device\": \"cuda\"})\n",
    "\n",
    "    print(\">\" * 10 + f\"\\tcurrent point: {best_params.number}\\tparams: {str(param)}\\t\" + \"<\" * 10)\n",
    "    \n",
    "    dtrain = xgb.DMatrix(train_select.drop(\"target\", axis=1), label=train_select[\"target\"])\n",
    "    dtest = xgb.DMatrix(oot_select.drop(\"target\", axis=1), label=oot_select[\"target\"])\n",
    "    \n",
    "    xgb_classifier = xgb.train(param, dtrain)\n",
    "    \n",
    "    print(KS(xgb_classifier.predict(dtrain), train_select[\"target\"]), KS(xgb_classifier.predict(dtest), oot_select[\"target\"]))\n",
    "    \n",
    "    temp = oot.assign(\n",
    "        score=lambda x: xgb_classifier.predict(\n",
    "            xgb.DMatrix(\n",
    "                x[train_select.drop(\"target\", axis=1).columns.tolist()].assign(\n",
    "                    客群状态1=lambda x: woe_encoder.transform(combiner.transform(x.客群状态1)),\n",
    "                )\n",
    "            )\n",
    "        )\n",
    "    )\n",
    "\n",
    "    # 查看尾部区分度\n",
    "    display(feature_bin_stats(\n",
    "        temp\n",
    "        , \"score\", method=\"step\", rules=temp[\"score\"].quantile([0.1, 0.3, 0.5, 0.7, 0.9, 0.95, 0.97, 0.98, 0.99]).unique().tolist()\n",
    "        , overdue=[f\"MOB{mob}\"], dpd=[dpd, 3, 0], max_n_bins=10, min_bin_size=0.001, return_cols=[\"坏样本数\", \"坏样本率\", \"LIFT值\", \"累积LIFT值\", \"分档KS值\"]\n",
    "    ))\n",
    "\n",
    "    # 通过自动分箱查看整体评分效果\n",
    "    display(feature_bin_stats(\n",
    "        temp\n",
    "        , \"score\", method=\"step\"\n",
    "        , overdue=[f\"MOB{mob}\"], dpd=[dpd, 3, 0], max_n_bins=10, min_bin_size=0.01, return_cols=[\"坏样本数\", \"坏样本率\", \"LIFT值\", \"累积LIFT值\", \"分档KS值\"]\n",
    "    ))\n",
    "\n",
    "    ks_plot(temp[\"score\"], temp[\"target\"], figsize=(10, 5), title=f\"TRAIL {best_params.number} OOT评分\");"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 封装评分卡模型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import toad\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from sklearn.base import BaseEstimator, TransformerMixin\n",
    "\n",
    "\n",
    "class ScorecardPredict(BaseEstimator, TransformerMixin):\n",
    "\n",
    "    def __init__(self, model, bad_rate=0.02, feature_names_in_=None):\n",
    "        from scorecardpipeline import StandardScoreTransformer\n",
    "        self.model = model\n",
    "        self.feature_names_in_ = feature_names_in_\n",
    "        self.classes_ = np.array([0])\n",
    "        self.status_1_map = {\"10\": 0.30365285, \"00\": -0.14935851, \"01\": -0.0913462}\n",
    "        self.bad_rate = bad_rate\n",
    "        self.scorecard = StandardScoreTransformer(base_score=100, pdo=200, bad_rate=self.bad_rate, greater_is_better=True, down_lmt=0, up_lmt=1000)\n",
    "\n",
    "    def processing_data(self, x):\n",
    "        import xgboost as xgb\n",
    "        return xgb.DMatrix(x.assign(客群状态1=lambda x: x[\"客群状态1\"].map(self.status_1_map))[self.feature_names_in_])\n",
    "\n",
    "    def predict_proba(self, x):\n",
    "        return self.scorecard.transform(self.model.predict(self.processing_data(x)).reshape(-1, 1))[:, 0]\n",
    "\n",
    "    def predict(self, x):\n",
    "        return self.predict_proba(x)\n",
    "\n",
    "    def transform(self, x, y=None):\n",
    "        return self.predict_proba(x)\n",
    "\n",
    "    def fit(self, x=None, y=None):\n",
    "        self.scorecard.fit(self.model.predict(self.processing_data(x)).reshape(-1, 1))\n",
    "        return self\n",
    "    \n",
    "    def __sklearn_is_fitted__(self):\n",
    "        return True\n",
    "\n",
    "\n",
    "scorecard = ScorecardPredict(xgb_classifier, feature_names_in_=train_select.drop(\"target\", axis=1).columns.tolist(), bad_rate=train_select[\"target\"].mean())\n",
    "scorecard.fit(oot)\n",
    "\n",
    "feature_bin_stats(\n",
    "    oot.assign(\n",
    "        模型分V2=lambda x: scorecard.predict_proba(x),\n",
    "    )\n",
    "    , \"模型分V2\", method=\"mdlp\", overdue=[f\"MOB{mob}\"], dpd=[dpd, 3, 0], max_n_bins=10, min_bin_size=0.01, return_cols=[\"坏样本数\", \"坏样本率\", \"LIFT值\", \"累积LIFT值\", \"分档KS值\"]\n",
    "    # , desc=f\"{oot_date} ~ {split_date} 样本数: {len(oot)} FPD7+: {oot['target'].sum()}\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 模型报告输出"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib agg\n",
    "# 切换为agg后在jupyter中不会显示图\n",
    "mob, dpd, oot_days = 1, 15, 15\n",
    "oot_date = (date.today() - relativedelta(months=mob, days=dpd + oot_days)).strftime(\"%Y-%m-%d\")\n",
    "split_date = (date.today() - relativedelta(months=mob, days=dpd)).strftime(\"%Y-%m-%d\")\n",
    "\n",
    "data_train = all_data.assign(\n",
    "    模型分V2=lambda x: scorecard.transform(x)\n",
    ").query(\"放款时间 < @oot_date\").assign(target=lambda x: (x[f\"MOB{mob}\"] > dpd).astype(int))\n",
    "\n",
    "feature_maps = {}\n",
    "percent_cols=[\"样本占比\", \"好样本占比\", \"坏样本占比\", \"坏样本率\", \"LIFT值\", \"坏账改善\", \"累积LIFT值\", \"累积坏账改善\"]\n",
    "condition_cols=[\"坏样本率\", \"LIFT值\"]\n",
    "merge_column=[\"指标名称\", \"指标含义\"]\n",
    "analyze_features = ['模型分V2']\n",
    "\n",
    "\n",
    "writer = ExcelWriter(system=\"windows\")\n",
    "\n",
    "rules = {\"模型分V2\": list(np.unique(np.quantile(scorecard.transform(oot), [0.01, 0.02, 0.03, 0.05, 0.1, 0.2, 0.4, 0.6, 0.8])))}\n",
    "auto_data_testing_report(\n",
    "    data_train\n",
    "    , features=analyze_features\n",
    "    , date=\"放款时间\"\n",
    "    , overdue=[f\"MOB{mob}\"]\n",
    "    , dpd=[dpd, 7, 3, 0]\n",
    "    , freq=\"W\"\n",
    "    , data_summary_comment=f\"训练样本: 放款时间 {data_train['放款时间'].min().strftime('%Y-%m-%d')} ~ {data_train['放款时间'].max().strftime('%Y-%m-%d')}，剔除 MOB1 > 3 且 <= 15 的样本，保证全部 MOB{mob} DPD{dpd}+ 有表现\"\n",
    "    , excel_writer=writer\n",
    "    , sheet=f\"TRAIN MOB{mob} DPD{dpd}+\"\n",
    "    , suffix=f\"训练样本集\"\n",
    "    , bin_params={\"method\": \"mdlp\", \"min_bin_size\": 0.005, \"rules\": rules, \"max_n_bins\": 10, \"return_cols\": [\"坏样本数\", \"坏样本占比\", \"坏样本率\", \"LIFT值\", \"累积LIFT值\", \"坏账改善\", \"分档KS值\", \"指标IV值\"]}\n",
    "    , feature_map=feature_maps\n",
    "    , pictures=['bin', 'ks']\n",
    ")\n",
    "\n",
    "\n",
    "data_oot = all_data.assign(\n",
    "    模型分V2=lambda x: scorecard.transform(x)\n",
    ").query(\"(放款时间 >= @oot_date) & (放款时间 < @split_date)\").assign(target=lambda x: (x[f\"MOB{mob}\"] > dpd).astype(int))\n",
    "\n",
    "end_row, end_col = auto_data_testing_report(\n",
    "    data_oot\n",
    "    , features=analyze_features\n",
    "    , date=\"放款时间\"\n",
    "    , overdue=[f\"MOB{mob}\"]\n",
    "    , dpd=[dpd, 7, 3, 0]\n",
    "    , freq=\"W\"\n",
    "    , data_summary_comment=f\"训练样本: 放款时间 {data_train['放款时间'].min().strftime('%Y-%m-%d')} ~ {data_train['放款时间'].max().strftime('%Y-%m-%d')}，不剔除灰，保证全部 MOB{mob} DPD{dpd}+ 有表现\"\n",
    "    , excel_writer=writer\n",
    "    , sheet=f\"OOT MOB{mob} DPD{dpd}+\"\n",
    "    , suffix=f\"跨时间验证集\"\n",
    "    , bin_params={\"method\": \"mdlp\", \"min_bin_size\": 0.005, \"rules\": rules, \"max_n_bins\": 5, \"return_cols\": [\"坏样本数\", \"坏样本占比\", \"坏样本率\", \"LIFT值\", \"累积LIFT值\", \"坏账改善\", \"分档KS值\", \"指标IV值\"]}\n",
    "    , feature_map=feature_maps\n",
    "    , pictures=['bin', 'ks'] \n",
    ")\n",
    "\n",
    "oot_table = feature_bin_stats(\n",
    "    data_oot\n",
    "    , \"模型分V2\", method=\"mdlp\", overdue=[\"MOB1\"], dpd=[7, 3, 0], max_n_bins=10, min_bin_size=0.01, return_cols=[\"坏样本数\", \"坏样本占比\", \"坏样本率\", \"LIFT值\", \"累积LIFT值\", \"坏账改善\", \"分档KS值\", \"指标IV值\"]\n",
    "    , desc=f\"\"\n",
    ")\n",
    "end_row, end_col = dataframe2excel(oot_table, writer, sheet_name=f\"OOT MOB{mob} DPD{dpd}+\", condition_color=\"F76E6C\", condition_cols=[\"坏样本率\", \"LIFT值\"], percent_cols=[\"坏样本占比\", \"坏样本率\", \"LIFT值\", \"累积LIFT值\"], title=\"MDLP自动分箱\", start_row=end_row + 2)\n",
    "\n",
    "# 模型上线需求\n",
    "test_units = data_oot[[\"授信编号\", \"模型分V2\"] + select_columns[:-1]].sample(n=100).reset_index(drop=True)\n",
    "feature_importances_dict = dict(zip(scorecard.model.feature_names_in_, scorecard.model.feature_importances_))\n",
    "\n",
    "features = []\n",
    "for i, f in enumerate(sorted(scorecard.feature_names_in_)):\n",
    "    features.append({\"数据供应商\": \"百融\" if f.startswith(\"als\") else \"其他\"\n",
    "                     , \"产品名称\": \"借贷意向验证\" if f.startswith(\"als\") else \"其他\"\n",
    "                     , \"特征名称\": f\n",
    "                     , \"特征含义\": feature_maps.get(f)\n",
    "                     , \"字段类型\": \"float\" if f not in data_oot[select_columns[:-1]].select_dtypes(object).columns else \"string\"\n",
    "                     , \"缺失值处理\": \"空字符串\\缺失值 转为 空值\"\n",
    "                     , \"特征贡献\": feature_importances_dict[f]\n",
    "                     , \"备注\": \"\"\n",
    "                    })\n",
    "\n",
    "features_info = pd.DataFrame(features).sort_values(\"特征贡献\", ascending=False).reset_index(drop=True).reset_index(names=\"序号\")\n",
    "end_row, end_col = dataframe2excel(features_info, writer, \"模型上线需求\", percent_cols=[\"特征贡献\"], title=\"入模变量信息\", condition_cols=[\"特征贡献\"], start_row=2)\n",
    "end_row, end_col = dataframe2excel(test_units, writer, \"模型上线需求\", title=\"测试用例\", start_row=end_row + 2)\n",
    "\n",
    "\n",
    "writer.save(f\"model_report/MOB{mob}DPD{dpd}建模报告-{date.today().strftime('%Y%m%d')}.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
